# -*- coding: utf-8 -*-
# 同步表结构
import configparser
import re
import sys
from collections import defaultdict, deque

from auto.untils import MySQLCursor


class MySQLSync:
    def __init__(self, database):
        self.database = database

    def get_connection(self, type):
        """
        建立数据库连接
        :param type:  1 表示源  2 表示target
        :return: 连接对象
        """
        config = configparser.ConfigParser()
        config.read("./config/config.ini", encoding='utf-8')

        if type == 1:
            db = MySQLCursor(
                host=config['source']['host'],
                port=int(config['source']['port']),
                user=config['source']['user'],
                password=config['source']['password'],
                database=self.database
            )
        else:
            db = MySQLCursor(
                host=config['database']['host'],
                port=int(config['database']['port']),
                user=config['database']['user'],
                password=config['database']['password'],
                database=self.database
            )
        return db.get_connection()

    def get_tables(self, conn):
        """获取所有表名"""
        with conn.cursor() as cursor:
            cursor.execute("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'")
            return [row[0] for row in cursor.fetchall()]

    def get_views(self, conn):
        """获取所有视图名"""
        with conn.cursor() as cursor:
            cursor.execute("SHOW FULL TABLES WHERE Table_type = 'VIEW'")
            return [row[0] for row in cursor.fetchall()]

    def get_triggers(self, conn):
        """获取所有触发器信息"""
        with conn.cursor() as cursor:
            cursor.execute("SHOW TRIGGERS")
            triggers = cursor.fetchall()
            result = []
            for trigger in triggers:
                result.append({
                    'trigger': trigger[0],  # Trigger
                    'event': trigger[1],  # Event
                    'table': trigger[2],  # Table
                    'statement': trigger[3],  # Statement
                    'timing': trigger[4],  # Timing
                    'created': trigger[5],  # Created
                    'sql_mode': trigger[6],  # sql_mode
                    'definer': trigger[7],  # Definer
                    'character_set_client': trigger[8],  # character_set_client
                    'collation_connection': trigger[9],  # collation_connection
                    'database_collation': trigger[10]  # Database Collation
                })
            return result

    def get_routines(self, conn):
        """获取所有存储过程和函数"""
        with conn.cursor() as cursor:
            # 查询information_schema获取存储过程和函数信息
            cursor.execute("""
                SELECT 
                    ROUTINE_NAME,
                    ROUTINE_TYPE,
                    DTD_IDENTIFIER,
                    ROUTINE_DEFINITION,
                    IS_DETERMINISTIC,
                    SQL_DATA_ACCESS,
                    SECURITY_TYPE,
                    ROUTINE_COMMENT,
                    DEFINER
                FROM information_schema.ROUTINES 
                WHERE ROUTINE_SCHEMA = DATABASE()
            """)
            routines = cursor.fetchall()
            result = []
            for routine in routines:
                result.append({
                    'name': routine[0],
                    'type': routine[1],  # PROCEDURE or FUNCTION
                    'data_type': routine[2],  # 返回值类型(函数)
                    'definition': routine[3],
                    'is_deterministic': routine[4],
                    'sql_data_access': routine[5],
                    'security_type': routine[6],
                    'comment': routine[7],
                    'definer': routine[8]
                })
            return result

    def get_trigger_ddl(self, conn, trigger_name):
        """获取触发器DDL"""
        with conn.cursor() as cursor:
            cursor.execute("SHOW CREATE TRIGGER `{}`".format(trigger_name))
            result = cursor.fetchone()
            return result[2] if result else None  # 第三列是CREATE TRIGGER语句

    def get_routine_ddl(self, conn, routine_name, routine_type):
        """获取存储过程或函数的DDL"""
        with conn.cursor() as cursor:
            if routine_type.upper() == 'PROCEDURE':
                cursor.execute("SHOW CREATE PROCEDURE `{}`".format(routine_name))
            else:  # FUNCTION
                cursor.execute("SHOW CREATE FUNCTION `{}`".format(routine_name))
            result = cursor.fetchone()
            return result[2] if result else None  # 第三列是CREATE语句

    def get_table_ddl(self, conn, table_name):
        """获取表结构DDL"""
        with conn.cursor() as cursor:
            cursor.execute("SHOW CREATE TABLE `{}`".format(table_name))
            result = cursor.fetchone()
            return result[1] if result else None

    def get_view_ddl(self, conn, view_name):
        """获取视图DDL"""
        with conn.cursor() as cursor:
            cursor.execute("SHOW CREATE VIEW `{}`".format(view_name))
            result = cursor.fetchone()
            return result[1] if result else None

    def extract_view_dependencies(self, view_ddl):
        """
        从视图DDL中提取依赖的表和视图
        返回: (依赖的表列表, 依赖的视图列表)
        """
        # 移除注释以减少干扰
        ddl_no_comments = re.sub(r'--.*?$', '', view_ddl, flags=re.MULTILINE)
        ddl_no_comments = re.sub(r'/\*.*?\*/', '', ddl_no_comments, flags=re.DOTALL)

        # 查找FROM和JOIN后面的表/视图名
        tables = set()
        views = set()

        # 正则表达式匹配FROM和JOIN子句
        patterns = [
            r'\bFROM\s+([`\w]+)(?:\s*,\s*([`\w]+))*(?:\s+(?:AS\s+)?\w+)?',
            r'\bJOIN\s+([`\w]+)(?:\s+(?:AS\s+)?\w+)?',
            r'\bINNER\s+JOIN\s+([`\w]+)',
            r'\bLEFT\s+JOIN\s+([`\w]+)',
            r'\bRIGHT\s+JOIN\s+([`\w]+)',
            r'\bFULL\s+JOIN\s+([`\w]+)'
        ]

        for pattern in patterns:
            matches = re.finditer(pattern, ddl_no_comments, re.IGNORECASE)
            for match in matches:
                for group in match.groups():
                    if group and group.strip():
                        # 移除反引号
                        name = group.replace('`', '').strip()
                        if name:
                            # 这里我们无法直接区分是表还是视图，先都加入views
                            # 在实际同步时会根据实际存在的对象类型来区分
                            views.add(name)

        return list(tables), list(views)

    def get_table_dependencies(self, conn):
        """
        获取数据库中所有表的外键依赖关系
        返回: {table_name: [list of tables it depends on (via FK)]}
        """
        with conn.cursor() as cursor:
            # 查询 information_schema 获取外键信息
            query = """
                SELECT 
                    TABLE_NAME,
                    REFERENCED_TABLE_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE 
                    TABLE_SCHEMA = DATABASE()
                    AND REFERENCED_TABLE_NAME IS NOT NULL
                ORDER BY TABLE_NAME;
            """
            cursor.execute(query)
            rows = cursor.fetchall()

            dependencies = defaultdict(list)
            all_tables_with_fk = set()

            for row in rows:
                table = row[0]
                referenced_table = row[1]
                dependencies[table].append(referenced_table)
                all_tables_with_fk.add(table)

            # 确保没有外键的表也出现在 dependencies 中（空列表）
            with conn.cursor() as c:
                c.execute("SHOW TABLES")
                all_tables = [t[0] for t in c.fetchall()]
                for table in all_tables:
                    if table not in dependencies:
                        dependencies[table] = []

            return dict(dependencies)

    def build_dependency_graph(self, conn, views):
        """
        构建视图依赖关系图
        返回: (依赖图, 反向依赖图)
        """
        dependency_graph = defaultdict(list)
        reverse_dependency_graph = defaultdict(list)

        view_ddls = {}

        # 获取所有视图的DDL
        for view in views:
            ddl = self.get_view_ddl(conn, view)
            if ddl:
                view_ddls[view] = ddl
                _, dependencies = self.extract_view_dependencies(ddl)

                for dep in dependencies:
                    if dep in views:  # 只考虑视图之间的依赖
                        dependency_graph[view].append(dep)
                        reverse_dependency_graph[dep].append(view)

        return dependency_graph, reverse_dependency_graph, view_ddls

    def topological_sort(self, dependency_graph, views):
        """
        对视图进行拓扑排序，确保依赖的视图先创建
        """
        # 计算入度
        in_degree = {view: 0 for view in views}
        for view in views:
            for dep in dependency_graph.get(view, []):
                if dep in in_degree:
                    in_degree[dep] += 1

        # 找到所有入度为0的节点
        queue = deque([view for view in views if in_degree[view] == 0])
        sorted_views = []

        while queue:
            current = queue.popleft()
            sorted_views.append(current)

            # 减少所有依赖当前视图的节点的入度
            for dependent in dependency_graph.get(current, []):
                if dependent in in_degree:
                    in_degree[dependent] -= 1
                    if in_degree[dependent] == 0:
                        queue.append(dependent)

        # 检查是否有循环依赖
        if len(sorted_views) != len(views):
            # 找到循环依赖的视图
            remaining = set(views) - set(sorted_views)
            print("警告: 发现可能的循环依赖，涉及视图: {}".format(', '.join(remaining)))
            # 将剩余的视图按字母顺序添加到末尾
            sorted_views.extend(sorted(remaining))

        return sorted_views

    def get_activiti_table_order(self):
        """
        Activiti 5.22 的推荐建表顺序
        """
        return [
            # 核心表
            "act_ge_property",  # 全局属性表
            "act_ge_bytearray",  # 通用字节数组表
            "act_re_deployment",  # 部署表
            "act_re_procdef",  # 流程定义表
            "act_re_model",  # 模型表
            # 运行时表
            "act_ru_execution",  # 执行表
            "act_ru_job",  # 作业表
            "act_ru_task",  # 任务表
            "act_ru_variable",  # 变量表
            "act_ru_identitylink",  # 身份联系表
            "act_ru_event_subscr",  # 事件订阅表
            # 历史表
            "act_hi_procinst",  # 历史流程实例表
            "act_hi_actinst",  # 历史活动实例表
            "act_hi_taskinst",  # 历史任务实例表
            "act_hi_varinst",  # 历史变量实例表
            "act_hi_detail",  # 历史详情表
            "act_hi_comment",  # 历史评论表
            "act_hi_attachment",  # 历史附件表
            "act_hi_identitylink",  # 历史身份联系表
            # 用户和组表
            "act_id_group",  # 用户组表
            "act_id_user",  # 用户表
            "act_id_membership",  # 成员关系表
            "act_id_info",  # 用户信息表
            # 其他表
            "act_procdef_info",  # 流程定义信息表
            "act_evt_log",  # 事件日志表
        ]

    def sync_tables(self):
        """同步表结构（按外键依赖顺序）"""
        try:
            with self.get_connection(1) as source_conn, self.get_connection(2) as target_conn:
                # 获取源库所有表
                source_tables = self.get_tables(source_conn)
                print("发现 {} 个表".format(len(source_tables)))

                if not source_tables:
                    print("没有需要同步的表")
                    return

                # 检测是否是 Activiti 表（包含 act_ 前缀的表）
                is_activiti = any(table.startswith('act_') for table in source_tables)

                if is_activiti:
                    print("检测到 Activiti 表，使用预定义建表顺序...")
                    # 使用预定义的 Activiti 建表顺序
                    activiti_order = self.get_activiti_table_order()
                    sorted_tables = [t for t in activiti_order if t in source_tables]
                    # 添加非 Activiti 表
                    other_tables = [t for t in source_tables if t not in sorted_tables]
                    sorted_tables.extend(other_tables)
                    print("Activiti 建表顺序: {}".format(', '.join(sorted_tables)))
                else:
                    # 获取依赖关系：table -> [tables it depends on]
                    dependencies = self.get_table_dependencies(source_conn)
                    # 拓扑排序（确保被引用的表先创建）
                    sorted_tables = self.topological_sort(dependencies, source_tables)
                    print("\n按依赖顺序创建表: {}".format(', '.join(sorted_tables)))

                # 获取目标库已有表
                target_tables = self.get_tables(target_conn)
                existing_set = set(target_tables)

                # 先删除目标库中所有存在的表（避免冲突）
                print("\n清理现有表...")

                # 关闭外键检查
                with target_conn.cursor() as cursor:
                    cursor.execute("SET FOREIGN_KEY_CHECKS=0;")

                for table in reversed(sorted_tables):  # 反向删除：先删有外键依赖的
                    if table in existing_set:
                        try:
                            with target_conn.cursor() as cursor:
                                cursor.execute("DROP TABLE IF EXISTS `{}`".format(table))
                            print("  删除表: {}".format(table))
                        except Exception as e:
                            print("  删除表 {} 失败: {}".format(table, e))

                target_conn.commit()

                # 按顺序创建表
                print("\n开始创建表...")
                success_count = 0
                failed_tables = []

                for table in sorted_tables:
                    ddl = self.get_table_ddl(source_conn, table)
                    if not ddl:
                        print("  无法获取表 {} 的DDL".format(table))
                        failed_tables.append(table)
                        continue

                    try:
                        with target_conn.cursor() as cursor:
                            # 移除原有的 AUTO_INCREMENT=xxx，并替换为 1
                            cleaned_ddl = re.sub(r'AUTO_INCREMENT=\d+', 'AUTO_INCREMENT=1', ddl)
                            # if not re.search(r'AUTO_INCREMENT=', cleaned_ddl):
                            #     # 如果没有找到，则添加进去
                            #     cleaned_ddl = re.sub(r'\) ENGINE=', ') ENGINE=InnoDB AUTO_INCREMENT=1 ', cleaned_ddl)
                            # 执行原始 DDL（含外键）
                            cursor.execute(cleaned_ddl)
                        print("  创建表: {} ✓".format(table))
                        success_count += 1
                    except Exception as e:
                        print("  创建表 {} 失败: {}".format(table, e))
                        failed_tables.append(table)

                target_conn.commit()

                # 恢复外键检查
                with target_conn.cursor() as cursor:
                    cursor.execute("SET FOREIGN_KEY_CHECKS=1;")

                # 输出结果
                print("\n表结构同步完成: 成功 {}/{}".format(success_count, len(source_tables)))
                if failed_tables:
                    print("失败的表: {}".format(', '.join(failed_tables)))

        except Exception as e:
            print("同步表结构时出错: {}".format(e))
            raise RuntimeError(e)

    def sync_views(self):
        """同步视图（按照依赖顺序）"""
        try:
            with self.get_connection(1) as source_conn, self.get_connection(2) as target_conn:
                source_views = self.get_views(source_conn)
                print("发现 {} 个视图".format(len(source_views)))

                if not source_views:
                    print("没有需要同步的视图")
                    return

                # 构建依赖关系图
                dependency_graph, reverse_dependency_graph, view_ddls = self.build_dependency_graph(source_conn,
                                                                                                    source_views)

                # 输出依赖关系信息
                print("\n视图依赖关系分析:")
                for view, deps in dependency_graph.items():
                    if deps:
                        print("  {} 依赖于: {}".format(view, ', '.join(deps)))

                # 拓扑排序
                sorted_views = self.topological_sort(dependency_graph, source_views)
                print("\n按依赖顺序创建视图: {}".format(', '.join(sorted_views)))

                # 先删除所有视图（避免依赖问题）
                print("\n清理现有视图...")
                for view in reversed(sorted_views):  # 反向删除，先删依赖别人的
                    try:
                        with target_conn.cursor() as cursor:
                            cursor.execute("DROP VIEW IF EXISTS `{}`".format(view))
                        print("  删除视图: {}".format(view))
                    except Exception as e:
                        print("  删除视图 {} 失败: {}".format(view, e))

                target_conn.commit()

                # 按依赖顺序创建视图
                print("\n创建视图...")
                success_count = 0
                failed_views = []

                for view in sorted_views:
                    ddl = view_ddls.get(view)
                    if ddl:
                        try:
                            with target_conn.cursor() as cursor:
                                cursor.execute(ddl)
                            print("  创建视图: {} ✓".format(view))
                            success_count += 1
                        except Exception as e:
                            print("  创建视图 {} 失败: {}".format(view, e))
                            failed_views.append(view)
                    else:
                        print("  无法获取视图 {} 的DDL".format(view))
                        failed_views.append(view)

                target_conn.commit()

                # 输出同步结果
                print("\n视图同步完成: 成功 {}/{}".format(success_count, len(source_views)))
                if failed_views:
                    print("失败的视图: {}".format(', '.join(failed_views)))

        except Exception as e:
            print("同步视图时出错: {}".format(e))
            raise RuntimeError(e)

    def sync_triggers(self):
        """同步触发器"""

        try:
            with self.get_connection(1) as source_conn, self.get_connection(2) as target_conn:
                source_triggers = self.get_triggers(source_conn)
                print("发现 {} 个触发器".format(len(source_triggers)))

                if not source_triggers:
                    print("没有需要同步的触发器")
                    return

                # 获取目标数据库现有的触发器
                target_triggers_info = self.get_triggers(target_conn)
                # target_triggers = {t['trigger'] for t in target_triggers_info}

                # 先删除所有现有的触发器
                print("\n清理现有触发器...")
                for trigger_info in target_triggers_info:
                    trigger_name = trigger_info['trigger']
                    try:
                        with target_conn.cursor() as cursor:
                            cursor.execute("DROP TRIGGER IF EXISTS `{}`".format(trigger_name))
                        print("  删除触发器: {}".format(trigger_name))
                    except Exception as e:
                        print("  删除触发器 {} 失败: {}".format(trigger_name, e))

                target_conn.commit()

                # 创建新的触发器
                print("\n创建触发器...")
                success_count = 0
                failed_triggers = []

                for trigger_info in source_triggers:
                    trigger_name = trigger_info['trigger']
                    ddl = self.get_trigger_ddl(source_conn, trigger_name)

                    if ddl:
                        try:
                            with target_conn.cursor() as cursor:
                                cursor.execute(ddl)
                            print(
                                "  创建触发器: {} (表: {}, 事件: {}, 时机: {}) ✓".format(
                                    trigger_name, trigger_info['table'], trigger_info['event'], trigger_info['timing']
                                ))
                            success_count += 1
                        except Exception as e:
                            print("  创建触发器 {} 失败: {}".format(trigger_name, e))
                            failed_triggers.append(trigger_name)
                    else:
                        print("  无法获取触发器 {} 的DDL".format(trigger_name))
                        failed_triggers.append(trigger_name)

                target_conn.commit()

                # 输出同步结果
                print("\n触发器同步完成: 成功 {}/{}".format(success_count, len(source_triggers)))
                if failed_triggers:
                    print("失败的触发器: {}".format(', '.join(failed_triggers)))

        except Exception as e:
            print("同步触发器时出错: {}".format(e))
            raise RuntimeError(e)

    def sync_routines(self):
        """同步存储过程和函数"""
        try:
            with self.get_connection(1) as source_conn, self.get_connection(2) as target_conn:
                source_routines = self.get_routines(source_conn)
                print("发现 {} 个存储过程和函数".format(len(source_routines)))

                if not source_routines:
                    print("没有需要同步的存储过程和函数")
                    return

                # 分离存储过程和函数
                procedures = [r for r in source_routines if r['type'] == 'PROCEDURE']
                functions = [r for r in source_routines if r['type'] == 'FUNCTION']

                print("  - 存储过程: {} 个".format(len(procedures)))
                print("  - 函数: {} 个".format(len(functions)))

                # 获取目标数据库现有的存储过程和函数
                target_routines = self.get_routines(target_conn)
                target_procedures = {r['name'] for r in target_routines if r['type'] == 'PROCEDURE'}
                target_functions = {r['name'] for r in target_routines if r['type'] == 'FUNCTION'}

                # 先删除所有现有的存储过程和函数
                print("\n清理现有存储过程和函数...")

                # 删除存储过程
                for proc_name in target_procedures:
                    try:
                        with target_conn.cursor() as cursor:
                            cursor.execute("DROP PROCEDURE IF EXISTS `{}`".format(proc_name))
                        print("  删除存储过程: {}".format(proc_name))
                    except Exception as e:
                        print("  删除存储过程 {} 失败: {}".format(proc_name, e))

                # 删除函数
                for func_name in target_functions:
                    try:
                        with target_conn.cursor() as cursor:
                            cursor.execute("DROP FUNCTION IF EXISTS `{}`".format(func_name))
                        print("  删除函数: {}".format(func_name))
                    except Exception as e:
                        print("  删除函数 {} 失败: {}".format(func_name, e))

                target_conn.commit()

                # 创建新的存储过程和函数
                print("\n创建存储过程和函数...")
                success_procedures = []
                failed_procedures = []
                success_functions = []
                failed_functions = []

                # 先创建函数（因为存储过程可能依赖函数）
                for func in functions:
                    func_name = func['name']
                    ddl = self.get_routine_ddl(source_conn, func_name, 'FUNCTION')

                    if ddl:
                        try:
                            with target_conn.cursor() as cursor:
                                cursor.execute(ddl)
                            print("  创建函数: {} (返回类型: {}) ✓".format(func_name, func.get('data_type', 'N/A')))
                            success_functions.append(func_name)
                        except Exception as e:
                            print("  创建函数 {} 失败: {}".format(func_name, e))
                            failed_functions.append(func_name)
                    else:
                        print("  无法获取函数 {} 的DDL".format(func_name))
                        failed_functions.append(func_name)

                # 再创建存储过程
                for proc in procedures:
                    proc_name = proc['name']
                    ddl = self.get_routine_ddl(source_conn, proc_name, 'PROCEDURE')

                    if ddl:
                        try:
                            with target_conn.cursor() as cursor:
                                cursor.execute(ddl)
                            print("  创建存储过程: {} ✓".format(proc_name))
                            success_procedures.append(proc_name)
                        except Exception as e:
                            print("  创建存储过程 {} 失败: {}".format(proc_name, e))
                            failed_procedures.append(proc_name)
                    else:
                        print("  无法获取存储过程 {} 的DDL".format(proc_name))
                        failed_procedures.append(proc_name)

                target_conn.commit()

                # 输出同步结果
                print("\n存储过程和函数同步完成:")
                print("  函数: 成功 {}/{}".format(len(success_functions), len(functions)))
                print("  存储过程: 成功 {}/{}".format(len(success_procedures), len(procedures)))

                if failed_functions:
                    print("失败的函数: {}".format(', '.join(failed_functions)))
                if failed_procedures:
                    print("失败的存储过程: {}".format(', '.join(failed_procedures)))

        except Exception as e:
            print("同步存储过程和函数时出错: {}".format(e))
            raise RuntimeError(e)

    def check_mysql_version(self, type):
        """检查MySQL版本，确保兼容性"""
        try:
            with self.get_connection(type) as conn:
                with conn.cursor() as cursor:
                    cursor.execute("SELECT VERSION()")
                    version = cursor.fetchone()[0]
                    print("数据库版本: {}".format(version))
                    # 检查是否为MySQL 5.7或更高版本
                    if "5.7" in version:
                        return True
                    else:
                        print("警告: 检测到MySQL版本 {}，脚本主要针对MySQL 5.7测试".format(version))
                        return True
        except Exception as e:
            print("检查MySQL版本时出错: {}".format(e))
            return False


def schema(database):
    # 创建同步对象
    sync = MySQLSync(database)

    # 检查版本兼容性
    if not sync.check_mysql_version(1):
        print("源数据库版本检查失败")
        return False

    if not sync.check_mysql_version(2):
        print("目标数据库版本检查失败")
        return False

    # 执行同步 - 注意顺序：先表，后视图，再触发器，最后存储过程和函数
    print(" 开始同步 {} 表结构...".format(database))
    sync.sync_tables()

    print("\n开始同步视图...".format(database))
    sync.sync_views()

    print("\n开始同步触发器...".format(database))
    sync.sync_triggers()

    print("\n开始同步存储过程和函数...".format(database))
    sync.sync_routines()

    print("\n所有同步任务完成！")

    return True


if __name__ == "__main__":
    schema("tt")
